from tkinter import *
from tkinter import ttk
import tkinter.messagebox
from tkinter import filedialog
import pandas as pd
from tkinter.scrolledtext import ScrolledText
import pymysql
import time
import numpy as np

root = Tk()

mean_text = StringVar()
std_text = StringVar()
num_text = StringVar()
max_text = StringVar()
min_text = StringVar()
industryfile_text = StringVar()
szzsfile_text = StringVar()
gpdffile_text = StringVar()
gpsjfile_text = StringVar()
gppjfile_text = StringVar()
gjsjfile_text = StringVar()
canculateratio_text = StringVar()


def open_industry_file():
    # print('test open_industry_file')
    filename = filedialog.askopenfilename()
    # print(filename)
    # print(type(filename))
    industryfile_text.set(filename)
    return filename


class read_inudstry():
    def read_industry_file():
        read_inudstry.data = pd.read_excel(industryfile_text.get())
        ratio_test = read_inudstry.data['SZZS_RATIO_NEW'].values
        # print('max min ', (ratio_test.max(), ratio_test.min()))
        numberChosen['values'] = list(set(read_inudstry.data['研究机构'].values))
        numberChosen.current(0)  # 设置下拉列表默认显示的值，0为 numberChosen['values'] 的下标值
        tkinter.messagebox.showinfo(message='数据读取完毕！')

    def raw():
        return read_inudstry.data


def read_stockfile():
    # print('test read_stockfile')
    filename = filedialog.askopenfilename()
    # print(filename)
    # print(type(filename))
    stockfile_text.set(filename)


def open_szzs_file():
    filename = filedialog.askopenfilename()
    szzsfile_text.set(filename)


class read_szzs():
    def read_szzs_file():
        read_szzs.data = pd.read_excel(szzsfile_text.get(), sheet_name='2016')
        # print('test_read_szz_file')
        tkinter.messagebox.showinfo(message='上证指数数据读取完毕！')

    def raw():
        # print('test_read_szzs_raw')
        return read_szzs.data


def open_gpdf_file():
    filename = filedialog.askopenfilename()
    gpdffile_text.set(filename)


class read_gpdf():
    def read_gpdf_file():
        read_gpdf.data = pd.read_excel(gpdffile_text.get())
        tkinter.messagebox.showinfo(message='股票得分排名数据读取完毕！')

    def raw():
        return read_gpdf.data


# 窗口布局大小为宽x高+水平偏移量+竖直偏移量
root.geometry('1535x800+0+0')

# 启动软件时窗口默认最大化
root.wm_state('zoomed')

# 设置标题栏名称
root.title('股票研报分析系统')
# 添加标题栏logo
# root.iconbitmap('favicon-20180817022753909.ico')

# 添加均值和置信度信息显示框
label_mean = Label(root, text='券商预测误差均值:')
label_mean.place(x=1010, y=130)
entry_mean = Label(root, textvariable=mean_text)
entry_mean.place(x=1010, y=150)
label_std = Label(root, text='券商预测置信度:')
label_std.place(x=1010, y=170)
entry_std = Label(root, textvariable=std_text)
entry_std.place(x=1010, y=190)

# 添加券商报告数量and最大/最小误差率
label_num = Label(root, text='该券商的评级报告总数为:')
label_num.place(x=1010, y=10)
entry_num = Label(root, textvariable=num_text)
entry_num.place(x=1010, y=30)
label_max = Label(root, text='该券商评级报告的最大误差率为:')
label_max.place(x=1010, y=50)
entry_max = Label(root, textvariable=max_text)
entry_max.place(x=1010, y=70)
label_min = Label(root, text='该券商评级报告的最小误差率为:')
label_min.place(x=1010, y=90)
entry_min = Label(root, textvariable=min_text)
entry_min.place(x=1010, y=110)

# 添加读取券商预测报告数据文件按钮
label_openindustryfile = Label(root, text='请选择券商预测数据文件位置：')
label_openindustryfile.place(x=2, y=540)
entry_openindustryfile = Label(root, textvariable=industryfile_text)
entry_openindustryfile.place(x=170, y=540)
button_openindustryfile = ttk.Button(root, text='打开券商预测报告数据', command=open_industry_file)
button_openindustryfile.place(x=890, y=537)
button_readindustryfile = ttk.Button(root, text='读取券商预测报告数据', command=read_inudstry.read_industry_file)
button_readindustryfile.place(x=1121, y=537)

# 准备一个大小为500*400的画布
canvas_ztfb = Canvas(root, bg="white", width=500, height=400)
canvas_ztfb.place(x=500, y=10)

industry_label = Label(root, text='请选择券商机构：')
industry_label.place(x=530, y=420)

# 创建一个下拉列表
number = StringVar()
numberChosen = ttk.Combobox(root, width=25, textvariable=number)
numberChosen.place(x=637, y=422)
numberChosen.get()

# 设置读取数据入口

# 设置起始日期
l1 = ttk.Label(root, text='请输入起始时间')
l1.place(x=1638, y=10)
year_1 = StringVar()
month_1 = StringVar()
day_1 = StringVar()

year_list_1 = ttk.Combobox(root, textvariable=year_1)
year_list_1['values'] = ('2010', '2011', '2012', '2013', '2014', '2015', '2016', '2017', '2018')
# year_list.grid(column=0, row=0)
year_list_1.current(0)
year_list_1.place(x=1600, y=30)

month_list_1 = ttk.Combobox(root, textvariable=month_1)
monthlist_1 = []
for i in range(1, 13):
    j = str(i)
    if len(j) == 1:
        j = str(0) + j
    monthlist_1.append(j)
month_list_1['values'] = tuple(monthlist_1)
month_list_1.current(0)
month_list_1.place(x=1600, y=60)

day_list_1 = ttk.Combobox(root, textvariable=day_1)
daylist_1 = []
for i in range(1, 32):
    j = str(i)
    if len(j) == 1:
        j = str(0) + j
    daylist_1.append(j)
day_list_1['values'] = tuple(daylist_1)
day_list_1.current(0)
day_list_1.place(x=1600, y=90)

# 截止日期
l2 = ttk.Label(root, text='请输入截止时间')
l2.place(x=1638, y=130)
year = StringVar()
month = StringVar()
day = StringVar()

year_list = ttk.Combobox(root, textvariable=year)
year_list['values'] = ('2010', '2011', '2012', '2013', '2014', '2015', '2016', '2017', '2018')
# year_list.grid(column=0, row=0)
year_list.current(0)
year_list.place(x=1600, y=150)

month_list = ttk.Combobox(root, textvariable=month)
monthlist = []
for i in range(1, 13):
    j = str(i)
    if len(j) == 1:
        j = str(0) + j
    monthlist.append(j)
month_list['values'] = tuple(monthlist)
month_list.current(0)
month_list.place(x=1600, y=180)

day_list = ttk.Combobox(root, textvariable=day)
daylist = []
for i in range(1, 32):
    j = str(i)
    if len(j) == 1:
        j = str(0) + j
    daylist.append(j)
day_list['values'] = tuple(daylist)
day_list.current(0)
day_list.place(x=1600, y=210)


def max_price():
    wind = pymysql.connect(host='124.127.117.59', port=9306, user='root', password='zhangwei2018Z!', db='wind',
                           charset='utf8')
    cursor = wind.cursor()
    if cursor:
        tkinter.messagebox.showinfo(message='数据库连接成功！')
    else:
        tkinter.messagebox.showinfo(message='数据库连接失败！')
    test = pd.read_excel('test_1.xlsx')
    tkinter.messagebox.showinfo(message='数据读取成功！')
    beg = int(year_list_1.get() + month_list_1.get() + day_list_1.get())
    endd = int(year_list.get() + month_list.get() + day_list.get())
    if endd < beg:
        raise ValueError('截止日期早于起始日期')
    test = test[test.Date >= beg]
    test = test[test.Date <= endd]

    gpdm = test['gpdm'].values
    date = test['Date'].values
    deadline = test['deadline'].values
    m = len(date)
    start1 = time.time()
    max_price = []
    for i in range(m):
        start = time.time()
        sql = "select cast(max(S_DQ_CLOSE) as char(10)) from ASHAREEODPRICES where TRADE_DT>='%d' and TRADE_DT<='%d' and S_INFO_WINDCODE like '%s' " % (
            int(date[i]), int(deadline[i]), str(gpdm[i]))
        cursor.execute(sql)
        maxi = cursor.fetchall()
        maxi = maxi[0][0]
        max_price.append(maxi)
        end = time.time()
        # print(end - start)
    end1 = time.time()
    tkinter.messagebox.showinfo(message='共耗时 ' + str(end1 - start1) + ' s！')
    # print(end1 - start1)
    # print(max_price)
    test['max_price'] = max_price
    test.to_excel('test_end.xlsx', index=False)
    tkinter.messagebox.showinfo(message='数据写入完毕！')
    cursor.close()
    tkinter.messagebox.showinfo(message='数据库连接已断开！')

    # 计算误差均值
    yjjg = test['yjjg'].values
    yjjg_1 = list(set(yjjg))
    n = len(yjjg_1)
    avgr = [0] * n
    for i in range(n):
        tmp = test['yjjg'] == yjjg_1[i]
        errori = np.mean(test[tmp]['error_ratio'].values)
        avgr[i] = errori
    data = pd.DataFrame()
    data['yjjg'] = yjjg_1
    data['avgr'] = avgr
    data.to_excel('yjjg_error.xlsx', index=False)
    avgr_error = [0] * m
    for i in range(m):
        for j in range(n):
            if yjjg[i] == yjjg_1[j]:
                avgr_error[i] = avgr[j]
    test['avgr_error'] = avgr_error
    test.to_excel('test_4_error.xlsx', index=False)
    tkinter.messagebox.showinfo(message='计算结束！')


b1 = Button(root, text='开始处理数据', command=max_price, bg="yellow", fg='red')
b1.place(x=1638, y=240)


# 画正态分布直方图
def drawPic():
    # 清楚历史图像残留
    raw_df = read_inudstry.raw()
    canvas_ztfb.delete(ALL)
    for i in range(1, 11):
        canvas_ztfb.create_line(i * 100, 0, i * 100, 400, fill='black', dash=(4, 4))
        canvas_ztfb.create_line(0, i * 40, 500, i * 40, fill='black', dash=(4, 4))
    raw_df = raw_df[raw_df.研究机构 == numberChosen.get()]
    ratio = raw_df['SZZS_RATIO_NEW'].values
    a = raw_df[raw_df.PREDICTED_PRICE <= raw_df.PRICE_MAX]
    std = len(a) / len(raw_df)
    mean_text.set(str(ratio.mean()))
    std_text.set(str(std))
    num_text.set(str(len(ratio)))
    max_text.set(str(ratio[abs(ratio).tolist().index(abs(ratio).max())]))
    min_text.set(str(ratio[abs(ratio).tolist().index(abs(ratio).min())]))
    # print('本券商总共有%d份报告' % len(h))
    # print('其中最大变化率为: ', h.max())
    # print('其中最大变化率为: ', h.min())
    # print('平均变化率为: ', h.mean())
    canvas_ztfb.create_line(250, 0, 250, 400, fill='black')
    if len(ratio) > 1:
        num = [0] * 500
        div = [0] * len(ratio)
        # print(ratio)
        for i in range(0, len(ratio)):
            div[i] = 51 + int(ratio[i] // ((1 - (-1)) / 100))
            # print(div[i])
            # print(num(div[i]))
            if num[div[i]] == 0:
                num[div[i]] = 1
            else:
                num[div[i]] += 1
    else:
        tkinter.messagebox.showinfo(message='报告数量少于或等于1份！')
    nummax = max(num)
    # print(nummax)
    # print(num)
    if len(ratio) > 1:
        dot_width = [0] * 100
        dot_height = [0] * 100
        for i in range(0, len(num)):
            if num[i] > 0:
                if i > 55 or i < 45:
                    canvas_ztfb.create_rectangle(i * (500 / 100), 400 - (400 / nummax) * num[i], (i + 1) * (500 / 100),
                                                 400, fill='blue')
                else:
                    canvas_ztfb.create_rectangle(i * (500 / 100), 400 - (400 / nummax) * num[i], (i + 1) * (500 / 100),
                                                 400, fill='red')
        for i in range(100):
            dot_width[i] = (500 / 100) * i + (500 / 100 / 2)
            dot_height[i] = 400 - (400 / nummax) * num[i]
            if num[i] > 0:
                if i > 0:
                    canvas_ztfb.create_line(dot_width[i], dot_height[i], dot_width[i - 1], dot_height[i - 1],
                                            fill='black')
    else:
        tkinter.messagebox.showinfo(message='报告数量少于或等于1份！')


def drawpic_szzs():
    canvas_szzs.delete(ALL)
    raw_df = read_szzs.raw()
    data = raw_df['NUM'].values
    canvas_szzs.create_line(0, 90, 490, 90, fill='red', dash=(4, 4))
    for i in range(0, len(data)):
        if 90 - data[i] / abs(data[abs(data).tolist().index(abs(data).max())]) * 80 < 90:
            canvas_szzs.create_rectangle(i * 490 / len(data),
                                         90 - data[i] / abs(data[abs(data).tolist().index(abs(data).max())]) * 80,
                                         490 / len(data) * (i + 1), 90, fill='red')
        else:
            canvas_szzs.create_rectangle(i * 490 / len(data),
                                         90 - data[i] / abs(data[abs(data).tolist().index(abs(data).max())]) * 80,
                                         490 / len(data) * (i + 1), 90, fill='green')


def show_gpdf():
    st = ''
    raw_df = read_gpdf.data
    stockinfo = raw_df.values
    for i in range(len(stockinfo)):
        st += ''.join(str(stockinfo[i][0]))
        st += '\t'
        st += ''.join(str(stockinfo[i][1]))
        st += '\n'
        if i == 9:
            st_num = len(st)
    scr_gpdf_1.insert(END, st[:st_num])
    scr_gpdf_2.insert(END, st[st_num:])


# 添加正态分布图显示界面
# 添加绘制正态分布直方图按钮
button_std = ttk.Button(root, text='绘制正态分布直方图', command=drawPic)
button_std.place(x=850, y=420)

# 添加上证指数显示模块
label_szzs = Label(root, text='上证指数变化')
label_szzs.place(x=205, y=192)
canvas_szzs = Canvas(root, bg='white', width=490, height=180)
canvas_szzs.place(x=5, y=10)
label_open_szzs_file = Label(root, text='请打开上证指数数据文件:')
label_open_szzs_file.place(x=2, y=503)
label_szzs_file = Label(root, textvariable=szzsfile_text)
label_szzs_file.place(x=143, y=503)
btn_open_szzs_file = ttk.Button(root, text='打开上证指数', command=open_szzs_file)
btn_open_szzs_file.place(x=890, y=507)
btn_read_szzs_file = ttk.Button(root, text='读取上证指数', command=read_szzs.read_szzs_file)
btn_read_szzs_file.place(x=1022, y=507)
btn_draw_szzs = ttk.Button(root, text='绘制上证指数变化图', command=drawpic_szzs)
btn_draw_szzs.place(x=1133, y=507)

# 添加股票得分显示模块
scr_gpdf_1 = ScrolledText(root, width=30, height=13, bg='white')
scr_gpdf_1.place(x=5, y=218)
scr_gpdf_2 = ScrolledText(root, width=30, height=13, bg='white')
scr_gpdf_2.place(x=265, y=218)
label_gpdf1210 = Label(root, text='1~10')
label_gpdf1210.place(x=80, y=396)
label_gpdf11220 = Label(root, text='11~20')
label_gpdf11220.place(x=350, y=396)
label_gpdf = Label(root, text='股票得分排名')
label_gpdf.place(x=205, y=396)
label_open_gpdf_file = Label(root, text='请打开股票得分数据文件:')
label_open_gpdf_file.place(x=2, y=478)
label_gpdf_file = Label(root, textvariable=gpdffile_text)
label_gpdf_file.place(x=143, y=478)
btn_open_gpdf_file = ttk.Button(root, text='打开股票得分', command=open_gpdf_file)
btn_open_gpdf_file.place(x=890, y=477)
btn_read_gpdf_file = ttk.Button(root, text='读取股票得分', command=read_gpdf.read_gpdf_file)
btn_read_gpdf_file.place(x=1022, y=477)
btn_show_gpdf = ttk.Button(root, text='显示股票得分排名', command=show_gpdf)
btn_show_gpdf.place(x=1145, y=477)


# 添加股票评级融合模块
# 添加读取股票信息文件数据函数
def open_gpsj_file():
    filename = filedialog.askopenfilename()
    gpsjfile_text.set(filename)


class read_gpsj():
    def read_gpsj_file():
        read_gpsj.data = pd.read_excel(gpsjfile_text.get())
        tkinter.messagebox.showinfo(message='股票数据已经读取完毕！')

    def raw():
        return read_gpsj.data


def open_gppj_file():
    filename = filedialog.askopenfilename()
    gppjfile_text.set(filename)


class read_gppj():
    def read_gppj_file():
        read_gppj.data = pd.read_excel(gppjfile_text.get())
        tkinter.messagebox.showinfo(message='券商评级数据已经读取完毕！')

    def raw():
        return read_gppj.data


class merge_gpsj_gppj():
    def merge_gpsj_gppj_file():
        pd.merge(read_gpsj.data, read_gppj.data, how='left').to_excel(
            str(gpsjfile_text.get()).strip('.xlsx') + '_已匹配券商评级.xlsx', index=False)
        merge_gpsj_gppj.output_path = str(gpsjfile_text.get()).strip('.xlsx') + '_已匹配券商评级.xlsx'
        tkinter.messagebox.showinfo(message='券商评级数据已经添加完毕！具体路径为:\n' + merge_gpsj_gppj.output_path)
        # print(merge_gpsj_gppj.output_path)


# 添加读取文件区域界面
label_open_gpsj_file = Label(root, text='请打开要添加券商评级的文件:')
label_open_gpsj_file.place(x=2, y=573)
entry_gpsj_file = Label(root, textvariable=gpsjfile_text)
entry_gpsj_file.place(x=175, y=573)

# 添加读取股票待处理数据文件按钮
button_open_gpsj_file = ttk.Button(root, text='打开股票文件', command=open_gpsj_file)
button_open_gpsj_file.place(x=890, y=570)
button_read_gpsj_file = ttk.Button(root, text='读取股票数据', command=read_gpsj.read_gpsj_file)
button_read_gpsj_file.place(x=1022, y=570)

# 添加读取文件区域界面
label_gppj_file = Label(root, text='请打开评级文件:')
label_gppj_file.place(x=2, y=598)
entry_gppj_file = Label(root, textvariable=gppjfile_text)
entry_gppj_file.place(x=105, y=598)

# 添加读取股票待处理数据文件按钮
button_open_gppj_file = ttk.Button(root, text='打开评级文件', command=open_gppj_file)
button_open_gppj_file.place(x=890, y=598)
button_read_gppj_file = ttk.Button(root, text='读取评级文件', command=read_gppj.read_gppj_file)
button_read_gppj_file.place(x=1022, y=598)

button_merge_gpsj_gppj_file = Button(root, text='一键匹配券商评级信息', width=17, height=2,
                                     command=merge_gpsj_gppj.merge_gpsj_gppj_file)
button_merge_gpsj_gppj_file.place(x=1122, y=573)


def open_gjsj_file():
    filename = filedialog.askopenfilename()
    gjsjfile_text.set(filename)


class read_gjsj():
    def read_gjsj_file():
        f = open(gjsjfile_text.get(), 'rb')
        read_gjsj.data = pd.read_csv(f)
        # print(read_gjsj.data.head())
        tkinter.messagebox.showinfo(message='股价数据已经读取完毕！')

    def raw():
        return read_gjsj.data


class merge_gpsj_gjsj():
    def merge_gpsj_gjsj_file():
        price_max = []
        df_gpsj = pd.read_excel(merge_gpsj_gppj.output_path)
        df_gpsj_date = df_gpsj['TRADE_DT'].values
        df_gjsj = read_gjsj.data
        merge_gpsj_gjsj.output_path = str(merge_gpsj_gppj.output_path).strip('.xlsx') + '_已匹配股价.xlsx'
        windcode = df_gpsj['S_INFO_WINDCODE'].values
        for i in range(len(windcode)):
            tmp = df_gjsj.S_INFO_WINDCODE == windcode[i]
            price = df_gjsj[tmp][df_gjsj.TRADE_DT >= df_gpsj_date[i]]
            price = price[df_gjsj.TRADE_DT <= df_gpsj['评级有效截止日期'].values[i]]
            price_max.append(price['S_DQ_CLOSE'].max())
            # print('这是第' + str(i) + '行股票信息')
            # print('---------------------------------------------')
            # 进度条控制函数
            bar['value'] = i
            root.update()
        df_gpsj['预测有效期内最大股价'] = price_max
        df_gpsj = pd.merge(df_gpsj, read_gjsj.data)
        df_gpsj.to_excel(merge_gpsj_gjsj.output_path, index=False)
        tkinter.messagebox.showinfo(message='股价数据已经匹配完毕！具体路径为:' + merge_gpsj_gjsj.output_path)
        # print(merge_gpsj_gjsj.output_path)


# 添加底部进度条
bar = ttk.Progressbar(root, length=1250, mode="determinate", orient=HORIZONTAL)
bar.place(x=2, y=776)
# bar["maximum"] = len(merge_gpsj_gjsj.windcode)
bar["value"] = 0

# 添加股价匹配程序
label_gjsj = Label(root, text='请打开股价完整数据文件:')
label_gjsj.place(x=2, y=631)
entry_gjsj_file = Label(root, textvariable=gjsjfile_text)
entry_gjsj_file.place(x=140, y=631)
button_open_gjsj_file = ttk.Button(root, text='打开股价文件', command=open_gjsj_file)
button_open_gjsj_file.place(x=890, y=630)
button_read_gjsj_file = ttk.Button(root, text='读取股价文件', command=read_gjsj.read_gjsj_file)
button_read_gjsj_file.place(x=1022, y=630)
button_merge_gpsj_gjsj_file = Button(root, text='一键匹配股价信息', width=17, command=merge_gpsj_gjsj.merge_gpsj_gjsj_file)
button_merge_gpsj_gjsj_file.place(x=1122, y=628)


# 添加计算误差率函数
def open_canculate_ratio_file():
    filename = filedialog.askopenfilename()
    canculateratio_text.set(filename)


class read_canculate_ratio():
    def read_canculate_ratio_file():
        read_canculate_ratio.data = pd.read_excel(canculateratio_text.get())
        tkinter.messagebox.showinfo(message='要计算预测误差均值的文件已经读取完毕！')

    def raw():
        return read_canculate_ratio.data


class canculate_ratio():
    def canculate_ratio_file():
        rate = []
        data = pd.read_excel(canculateratio_text.get())
        data_rate = data['取最小值'].values
        for i in range(len(data_rate)):
            if data_rate[i] < 0:
                rate.append(data_rate[i] * 1.5)
            else:
                rate.append(data_rate[i] * 1)
        data['加惩罚系数后的评级数值'] = rate
        data['预测股价'] = (1 + data['加惩罚系数后的评级数值']) * data['S_DQ_CLOSE_BEGIN']
        data['预测有效期内最大股价'] = data['PRICE_MAX']
        data['RATIO_TEST'] = (data['预测股价'] - data['预测有效期内最大股价']) / data['预测有效期内最大股价']
        canculate_ratio.output_path = str(canculateratio_text.get()).strip('.xlsx') + '_已添加误差率.xlsx'
        data.to_excel(canculate_ratio.output_path, index=False)
        tkinter.messagebox.showinfo(
            message='误差率已添加完毕！具体路径为:' + canculate_ratio.output_path)


# 添加计算预测误差及置信度模块
label_canculate_ratio = Label(root, text='请打开要计算误差均值和置信度的文件:')
label_canculate_ratio.place(x=2, y=666)
entry_canculate_ratio = Label(root, textvariable=canculateratio_text)
entry_canculate_ratio.place(x=220, y=666)
button_open_wcjz_file = ttk.Button(root, text='打开文件计算误差均值和置信度', command=open_canculate_ratio_file)
button_open_wcjz_file.place(x=890, y=665)
button_read_wcjz_file = ttk.Button(root, text='读取文件计算误差均值和置信度',
                                   command=read_canculate_ratio.read_canculate_ratio_file)
button_read_wcjz_file.place(x=1074, y=665)
button_canculate_ratio = Button(root, text='一键计算预测误差及置信度', command=canculate_ratio.canculate_ratio_file)
button_canculate_ratio.place(x=890, y=695)


# 添加提取误差率及置信度模块
def ratio_info():
    df_ratio_info = pd.read_excel(canculate_ratio.output_path)
    # data = df_ratio_info['研究机构'].values
    # num = []
    # ratioandtrue = []
    # for i in range(len(data)):
    #     a = df_ratio_info[df_ratio_info.研究机构 == data[i]]
    #     num.append(len(a))
    #     ratio = a['RATIO'].values.mean()
    #     ratioandtrue.append(ratio)
    df_ratio_info_mean = df_ratio_info.groupby('研究机构')['RATIO'].agg(np.mean)
    df_ratio_info_true = df_ratio_info.groupby('研究机构')['SZZS_RATIO_CONFIDENCE'].agg(np.mean)
    df_ratio_info_mean.to_excel(str(canculate_ratio.output_path).strip('.xlsx') + '_已提取均值.xlsx')
    df_ratio_info_true.to_excel(str(canculate_ratio.output_path).strip('.xlsx') + '_已提取置信度.xlsx')
    df_ratio_info_mean = pd.read_excel(str(canculate_ratio.output_path).strip('.xlsx') + '_已提取均值.xlsx')
    df_ratio_info_true = pd.read_excel(str(canculate_ratio.output_path).strip('.xlsx') + '_已提取置信度.xlsx')
    data = pd.merge(df_ratio_info_mean, df_ratio_info_true)
    data.to_excel(str(canculate_ratio.output_path).strip('.xlsx') + '_已提取均值&置信度.xlsx', index=False)
    tkinter.messagebox.showinfo(
        message='均值和置信度已提取成功！具体路径为：' + str(canculate_ratio.output_path).strip('.xlsx') + '_已提取均值&置信度.xlsx')


button_ratio_info = Button(root, text='一键提取预测误差率和置信度', command=ratio_info)
button_ratio_info.place(x=1084, y=695)


# 添加计算股票得分模块
def canculate_stockscore():
    pass


button_canculate_stockscore = Button(root, text='一键计算股票得分', command=canculate_stockscore)
button_canculate_stockscore.place(x=890, y=730)


# 添加股票得分匹配股价模块
def merge_gpdfpm_gj():
    pass


button_merge_gpdfpm_gj = Button(root, text='一键匹配前20名股价', command=merge_gpdfpm_gj)
button_merge_gpdfpm_gj.place(x=1004, y=730)


# 添加股票排名填充模块
def fill_gpdfpm_zdf():
    pass


button_fill_gpdfpm_zdf = Button(root, text='一键填充股票涨跌幅', command=fill_gpdfpm_zdf)
button_fill_gpdfpm_zdf.place(x=1132, y=730)

scr_print = ScrolledText(root, width=36, height=61, bg='white')
scr_print.place(x=1255, y=2)
scr_print.insert(END, '完整的步骤：\n\n\
1.在评级数据总表里筛选出XXXX年份的评级数据\n\n\
2.对筛选出的评级数据进行处理，剔除无用的列然后按股票代码+发布日期进行排序\n\n\
3.根据报告发布日期求预测截止日期，注意2017.07往后的日期要做特殊处理\n\n\
4.使用程序匹配评级数值\n\n\
5.使用程序匹配评级报告发布当天收盘价、预测期内最高价\n\n\
6.对生成新文件进行处理，剔除无用的列\n\n\
7.使用程序计算预测误差率和置信度\n\n\
8.选取下一年份的评级数据并重复1 - 4操作\n\n\
9.计算预测时间系数\n\n\
10.用程序匹配上一年的预测误差率和置信度\n\n\
11.求股票得分并筛选出前20名\n\n\
12.匹配股价并求涨跌幅填入表中\n\n\
')

root.mainloop()
